import pandas as pd

default_file_name = "0421薇诺娜信息流计划搭建2.xlsx"

# 设置显示所有列（默认只显示部分列）
pd.set_option('display.max_columns', None)

# 设置显示所有行（默认只显示部分行）
pd.set_option('display.max_rows', None)

# 设置列宽（防止内容被截断）
pd.set_option('display.max_colwidth', 100)  # 设置每列最大显示宽度
def get_ad_config(row):
    if row['路径名称'] == "自点":
        return {
            'ad_type': '搜索推广',
            'promotion_target': '点击量',
            'bidding_type': '自动出价',
            'trans_platform': '',
            'cost_ctr_type': '自动控制 ',
            'ad_group': '不加入'
        }
    elif row['路径名称'] == "手点":
        return {
            'ad_type': '搜索推广',
            'promotion_target': '点击量',
            'bidding_type': '手动出价',
            'trans_platform': '',
            'cost_ctr_type': '',
            'ad_group': '不加入'
        }
    elif row['路径名称'] == "自点1":
        return {
            'ad_type': '搜索推广',
            'promotion_target': '点击量',
            'bidding_type': '自动出价',
            'trans_platform': '',
            'cost_ctr_type': '点击成本控制 ',
            'ad_group': '不加入'
        }
    elif row['路径名称'] == "soc自":
        return {
            'ad_type': '搜索推广',
            'promotion_target': '点击份额（SOC）',
            'bidding_type': '自动出价',
            'trans_platform': '',
            'cost_ctr_type': '点击成本控制 ',
            'ad_group': '不加入'
        }
    elif row['路径名称'] == "soc手":
        return {
            'ad_type': '搜索推广',
            'promotion_target': '点击份额（SOC）',
            'bidding_type': '手动出价',
            'trans_platform': '',
            'cost_ctr_type': '',
            'ad_group': '不加入'
        }
    elif row['路径名称'] == "站自":
        return {
            'ad_type': '搜索推广',
            'promotion_target': '站外转化量',
            'bidding_type': '自动出价',
            'trans_platform': '淘联-小红星',
            'cost_ctr_type': '点击成本控制 ',
            'ad_group': '不加入'
        }
    elif row['路径名称'] == "站盟":
        return {
            'ad_type': '搜索推广',
            'promotion_target': '站外转化量',
            'bidding_type': '自动出价',
            'trans_platform': '京东-小红盟',
            'cost_ctr_type': '点击成本控制 ',
            'ad_group': '不加入'
        }
    elif row['路径名称'] == "站自自":
        return {
            'ad_type': '搜索推广',
            'promotion_target': '站外转化量',
            'bidding_type': '自动出价',
            'trans_platform': '淘联-小红星',
            'cost_ctr_type': '自动控制 ',
            'ad_group': '不加入'
        }

def get_set_note_ids(df=None, file_path=None):
    note_ids = []
    for item in df['笔记ID'].tolist():
        ids = [x.strip() for x in item.split('\n') if x.strip()]
        note_ids.extend(ids)

    search_comp = get_search_comp(file_path)  # 转化组件中的笔记ID
    comp_note_ids = [str(note_id).strip() for note_id in search_comp['笔记ID'].tolist()]

    main_set = set(note_ids)
    comp_set = set(comp_note_ids)

    missing_ids = main_set - comp_set
    return missing_ids  # 主表中有但是附表中不存在的笔记ID

def get_complete_sheet1(file_path=None):
    if file_path is None:
        file_path = default_file_name  # 运行时动态获取当前值

    df = pd.read_excel(file_path, sheet_name="计划列表", engine='openpyxl')  # sheet_name 可省略
    config_df = df.apply(get_ad_config, axis=1, result_type='expand')
    df = pd.concat([df, config_df], axis=1)
    return df


def get_complete_sheet_platform(file_path=None):
    try:
        if file_path is None:
            file_path = default_file_name  # 运行时动态获取当前值

        df = pd.read_excel(file_path, sheet_name="平台精选")
        cols_to_fill = ['人群定向包']
        df[cols_to_fill] = df[cols_to_fill].ffill()
        # print(df['人群定向包'].tolist())
        # print(df['小类'].tolist())
        print(df)
        # print(df['一级选项'].str.split('\n').explode().tolist())
        return df
    except ValueError as e:
        if "Worksheet named '平台精选' not found" in str(e):
            print(f"警告: 文件 {file_path} 中不存在[平台精选]sheet")
            return pd.DataFrame()  # 返回带列名的空DF
        else:
            raise  # 重新抛出其他异常
    except Exception as e:
        print(f"读取文件失败: {e}")
        return pd.DataFrame()

def get_complete_sheet_indus_people(file_path=None):
    """
    读取行业人群sheet，若不存在返回空DataFrame
    """
    try:
        if file_path is None:
            file_path = default_file_name  # 运行时动态获取当前值

        df = pd.read_excel(file_path, sheet_name="行业人群")
        # 处理合并单元格
        cols_to_fill = ['人群定向包', '小类', '一级选项']
        df[cols_to_fill] = df[cols_to_fill].ffill()
        print("成功加载数据:")
        print(df['人群定向包'].tolist())
        print(df.head())
        return df
    except ValueError as e:
        if "Worksheet named '行业人群' not found" in str(e):
            print(f"警告: 文件 {file_path} 中不存在[行业人群]sheet")
            return pd.DataFrame()  # 返回带列名的空DF
        else:
            raise  # 重新抛出其他异常
    except Exception as e:
        print(f"读取文件失败: {e}")
        return pd.DataFrame()
def get_complete_sheet_indus_inters(file_path=None):
    """
    读取行业兴趣sheet，若不存在返回空DataFrame
    """
    try:
        if file_path is None:
            file_path = default_file_name  # 运行时动态获取当前值

        df = pd.read_excel(file_path, sheet_name="行业兴趣")
        # 处理合并单元格
        cols_to_fill = ['人群定向包', '小类', '一级选项']
        df[cols_to_fill] = df[cols_to_fill].ffill()
        print(df)
        return df
    except ValueError as e:
        if "Worksheet named '行业兴趣' not found" in str(e):
            print(f"警告: 文件 {file_path} 中不存在[行业兴趣]sheet")
            return pd.DataFrame()  # 返回空DF
        else:
            raise
    except Exception as e:
        print(f"读取文件失败: {e}")
        return pd.DataFrame()
def get_complete_sheet_keyword(file_path=None):
    """
    读取关键词sheet，若不存在返回空DataFrame
    """
    try:
        if file_path is None:
            file_path = default_file_name  # 运行时动态获取当前值

        df = pd.read_excel(file_path, sheet_name="关键词").dropna(how="all")
        cols_to_fill = ['人群定向包']
        df[cols_to_fill] = df[cols_to_fill].ffill()
        print(df)
        return df
    except ValueError as e:
        if "Worksheet named '关键词' not found" in str(e):
            print(f"警告: 文件 {file_path} 中不存在[关键词]sheet")
            return pd.DataFrame()  # 返回空DF
        else:
            raise
    except Exception as e:
        print(f"读取文件失败: {e}")
        return pd.DataFrame()
def get_complete_sheet_crowd_pack(file_path=None):
    """
    读取关键词sheet，若不存在返回空DataFrame
    """
    try:
        if file_path is None:
            file_path = default_file_name  # 运行时动态获取当前值

        df = pd.read_excel(file_path, sheet_name="人群包")
        print(df)
        return df
    except ValueError as e:
        if "Worksheet named '人群包' not found" in str(e):
            print(f"警告: 文件 {file_path} 中不存在[人群包]sheet")
            return pd.DataFrame()  # 返回空DF
        else:
            raise
    except Exception as e:
        print(f"读取文件失败: {e}")
        return pd.DataFrame()

def get_search_comp(file_path=None):
    """
    读取搜索组件sheet，若不存在返回空DataFrame
    """
    try:
        if file_path is None:
            file_path = default_file_name  # 运行时动态获取当前值

        df = pd.read_excel(file_path, sheet_name="转化组件")
        return df
    except ValueError as e:
        if "Worksheet named '搜索组件' not found" in str(e):
            print(f"警告: 文件 {file_path} 中不存在[搜索组件]sheet")
            return pd.DataFrame()  # 返回空DF
        else:
            raise
    except Exception as e:
        print(f"读取文件失败: {e}")
        return pd.DataFrame()